package com.tjhq.commons.dict.db.service.impl;

import com.tjhq.commons.dict.db.service.IDBTableService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author pc
 * @date 2017/11/8
 */
@Service
public class DBTableService implements IDBTableService{
    public static final String TABLE_TRIGGER_SQL =
            "DROP TRIGGER IF EXISTS TR_@tableName@_INSERT; " +
            "CREATE TRIGGER TR_@tableName@_INSERT " +
            "BEFORE INSERT ON @tableName@ FOR EACH ROW " +
            "BEGIN " +
            "    IF (NEW.PROVINCE IS NULL || LENGTH(TRIM(NEW.PROVINCE))<1) THEN " +
            "    SET NEW.PROVINCE:=GLOBAL_GETPARAM('DIVID'); " +
            "    END IF; " +
            "    IF (NEW.YEAR IS NULL || LENGTH(TRIM(NEW.YEAR))<1) THEN " +
            "    SET NEW.YEAR:=GLOBAL_GETPARAM('YEAR'); " +
            "    END IF; " +
            "    IF (NEW.DATAKEY IS NULL || LENGTH(TRIM(NEW.DATAKEY))<1) THEN " +
            "    SET NEW.DATAKEY:=REPLACE(uuid(),'-',''); " +
            "    END IF;" +
            " @taskTrigger@ " +
            "    IF (NEW.DBVERSION IS NULL || LENGTH(TRIM(NEW.DBVERSION))<1) THEN " +
            "    SET NEW.DBVERSION := CASE WHEN DATE_FORMAT(NEW.DBVERSION, 'YYYY-MM-DD') = '2012-01-01' THEN DATE_FORMAT('2012-01-01', 'YYYY-MM-DD') ELSE SYSDATE() END; " +
            "    END IF; " +
            "END; " +
            
            "DROP TRIGGER IF EXISTS TR_@tableName@_UPDATE; " +
            "CREATE TRIGGER TR_@tableName@_UPDATE " +
            "BEFORE UPDATE ON @tableName@ FOR EACH ROW " +
            "BEGIN " +
            "    SET NEW.DBVERSION:=SYSDATE(); " +
            "END; ";
    
    private static final String TABLE_TASK_TRIGGER =
            "    IF (NEW.TASKID IS NULL || LENGTH(TRIM(NEW.TASKID))<1) THEN " +
            "    SET NEW.TASKID:=GLOBAL_GETPARAM('TASKID'); " +
            "    END IF; ";
    

    @Override
    public String createTrigger(String tableName, boolean isTask) {
        String sql = TABLE_TRIGGER_SQL.replaceAll("@tableName@", tableName);
        if (isTask) {
            sql = sql.replaceAll("@taskTrigger@", TABLE_TASK_TRIGGER);
        } else {
            sql = sql.replaceAll("@taskTrigger@", "");
        }
        return sql;
    }
    
    private static final String TABLE_PRIMARYKEY_SQL = "ALTER TABLE @tableName@ ADD PRIMARY KEY  PK_@tableName@ (@primaryKeys@);";
    
    @Override
    public String createPK(String tableName, List<String> listPK) {
        if (listPK == null || listPK.size() == 0) {
            return "";
        }
        String sql = TABLE_PRIMARYKEY_SQL.replaceAll("@tableName@", tableName);
        String keys = StringUtils.join(listPK,",");
        sql = sql.replaceAll("@primaryKeys@", keys);
        return sql;
    }

}
